import requests
from bs4 import BeautifulSoup
import mysql.connector
from datetime import datetime
import re

# === 配置项 ===
CITY_NAME = '昆明'
CITY_CODE = '56778'
DB_CONFIG = {
    'host': 'localhost',
    'user': 'root',
    'password': '123456',  # ← 修改为你自己的密码
    'database': 'weather_db'
}
headers = {
    'user-agent': 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/133.0.0.0 Safari/537.36'
}

# === 创建数据库和表 ===
def create_database_and_tables():
    try:
        # 连接到 MySQL
        conn = mysql.connector.connect(host=DB_CONFIG['host'], user=DB_CONFIG['user'], password=DB_CONFIG['password'])
        cursor = conn.cursor()

        # 创建数据库（如果不存在）
        cursor.execute(f"CREATE DATABASE IF NOT EXISTS {DB_CONFIG['database']}")
        cursor.execute(f"USE {DB_CONFIG['database']}")

        # 创建表（如果不存在）
        cursor.execute("""
        CREATE TABLE IF NOT EXISTS current_weather (
            id INT AUTO_INCREMENT PRIMARY KEY,
            city VARCHAR(50),
            nowTemp VARCHAR(50),
            nowWeather VARCHAR(50),
            totayTemp VARCHAR(50),
            wind VARCHAR(50),
            humidity VARCHAR(50),
            Ultraviolet VARCHAR(50),
            atmospheric VARCHAR(50),
            date_time DATETIME
        )
        """)

        cursor.execute("""
        CREATE TABLE IF NOT EXISTS forecast_weather (
            id INT AUTO_INCREMENT PRIMARY KEY,
            city VARCHAR(50),
            date_str VARCHAR(50),
            time VARCHAR(50),
            weather VARCHAR(50),
            wind VARCHAR(50),
            windPower VARCHAR(50),
            air VARCHAR(50)
        )
        """)

        conn.commit()
        print("✅ 数据库和表已创建")
    except Exception as e:
        print("❌ 创建数据库和表失败：", e)
    finally:
        if conn.is_connected():
            cursor.close()
            conn.close()

# === 当前天气 ===
def get_current_weather():
    url = f'https://tianqi.2345.com/kunming2d/{CITY_CODE}.htm'
    try:
        res = requests.get(url, headers=headers, timeout=10)
        res.encoding = 'utf-8'
        soup = BeautifulSoup(res.text, 'html.parser')
        nowTemp = soup.select_one('.real-t').text.strip() + 'C'  # 当前温度
        nowWeather = soup.select_one('.cludy').text.strip()  # 当前气候
        totayTemp = soup.select_one('.real-today').text.strip().replace('\xa0', ' ')  # 今日天气
        humidity = soup.select_one('.real-data li').contents
        humidityList = []
        for item in humidity:
            content = item.text
            humidityList.append(content)

        wind = humidityList[1].replace('\xa0', ' ')  # 风
        humidity = humidityList[3].replace('\xa0', ' ')  # 湿度
        Ultraviolet = humidityList[5].replace('\xa0', ' ')  # 紫外线
        atmospheric = humidityList[7].replace('\xa0', ' ')  # 气压

        return {
            'nowTemp': nowTemp,# 当前温度
            'nowWeather': nowWeather,# 当前气候
            'totayTemp': totayTemp,# 今日天气
            'wind': wind,# 风
            'humidity': humidity,# 湿度
            'Ultraviolet': Ultraviolet,# 紫外线
            'atmospheric': atmospheric,# 气压
            'date_time': datetime.now() # 今日时间
        }
    except Exception as e:
        print("❌ 当前天气获取失败：", e)
        return None


# === 未来天气 ===
def get_forecast_weather():
    url = f'https://tianqi.2345.com/kunming/{CITY_CODE}.htm'
    try:
        res = requests.get(url,headers=headers, timeout=10)
        res.encoding = 'utf-8'
        soup = BeautifulSoup(res.text, 'html.parser')
        days = soup.select('.hours24-list-item')
        # print(days)
        items=[]
        results = []


        for day in days:
            day=day.contents

            date_str = day[1].text
            time = day[3].text
            weather=day[7].text
            wind=day[9].text
            windPower = day[11].text
            air=day[13].text

            results.append({
                'date_str':date_str,
                'time': time,
                'weather': weather,
                'wind': wind,
                'windPower': windPower,
                'air':air
            })
        return results
    except Exception as e:
        print("❌ 未来天气获取失败：", e)
        return []



# === 保存到数据库 ===
def save_data_to_mysql(data, table_name):
    try:
        conn = mysql.connector.connect(**DB_CONFIG)
        cursor = conn.cursor()

        if table_name == 'current_weather':
            item = data
            cursor.execute(f"""
                INSERT INTO {table_name} (city, nowTemp,nowWeather,totayTemp,wind,humidity,Ultraviolet,atmospheric,date_time)
                VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s)
            """, (CITY_NAME, item['nowTemp'], item['nowWeather'], item['totayTemp'], item['wind'], item['humidity'], item['Ultraviolet'], item['atmospheric'], item['date_time']))

        elif table_name == 'forecast_weather':
            for item in data:
                cursor.execute(f"""
                    INSERT INTO {table_name} (city, date_str,time,weather,wind,windPower,air)
                    VALUES (%s, %s, %s, %s, %s, %s,%s)
                """, (
                    CITY_NAME, item['date_str'], item['time'], item['weather'],
                    item['wind'],item['windPower'],item['air']))

        conn.commit()
        print(f"✅ 已保存到 {table_name}")
    except Exception as e:
        print(f"❌ 保存到 {table_name} 出错：", e)
    finally:
        if conn.is_connected():
            cursor.close()
            conn.close()


# === 主函数 ===
def main():
    # 创建数据库和表
    create_database_and_tables()

    # 当前天气
    current = get_current_weather()
    if current:
        save_data_to_mysql(current, 'current_weather')

    # 未来天气
    forecast = get_forecast_weather()
    if forecast:
        save_data_to_mysql(forecast, 'forecast_weather')

if __name__ == '__main__':
    main()